﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Healthy
{
    public partial class frmservice1 : Form
    {
        public frmservice1()
        {
            InitializeComponent();
        }

        private void frmservice1_Load(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            autoid();
            showType();
            //autoid1();
        }
        private void showType()
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sqlType;
            sqlType = "select * from osm";

            SqlDataAdapter da;
            DataSet ds = new DataSet();
            da = new SqlDataAdapter(sqlType, Conn);
            da.Fill(ds, "osm");

            osmid.DataSource = ds.Tables["osm"];
            osmid.ValueMember = "OSMID";
            osmid.DisplayMember = "OSMNAM";


            string sqlType1;
            sqlType1 = "select * from madecine ";

            //SqlDataAdapter da;
            //DataSet ds = new DataSet();
            da = new SqlDataAdapter(sqlType1, Conn);
            da.Fill(ds, "madecine");

            medid.DataSource = ds.Tables["madecine"];
            medid.ValueMember = "MEDCCD";
            medid.DisplayMember = "MEDCNM";



        }
        private void button1_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string strdate1;
            DateTime datet;
            strdate1 = Convert.ToString(DateTime.Now.Year) + "-" + Convert.ToString(DateTime.Now.Month) + "-" + Convert.ToString(DateTime.Now.Day);
            datet = datewd.Value;

            string query = "insert into withdraw(WDID,OSMID,DATECD) values('" + wdid.Text + "'  , '" + osmid.SelectedValue + "', '" + strdate1 + "')";
            SqlCommand cmd = new SqlCommand(query, Conn);
            cmd.ExecuteNonQuery();
            //Use ExecuteNonQuery to insert data.   
            MessageBox.Show("ข้อมูลถูกบันทึกเรียบร้อยแล้ว");


            string sql1 = "SELECT WDID,OSMNAM,DATECD FROM  withdraw,osm where withdraw.OSMID=osm.OSMID  ";
            SqlCommand cmd1 = new SqlCommand(sql1, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
            DataSet ds = new DataSet();
            //autoid();

        }

        private void button2_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=NARMA-PC\SQLEXPRESS;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();


            string query = "delete from withdraw where WDID = '" + wdid.Text + "'";
            SqlCommand cmd = new SqlCommand(query, Conn);
            cmd.ExecuteNonQuery();

            string sql = "SELECT WDID,OSMNAM,DATECD FROM  withdraw,osm where withdraw.OSMID=osm.OSMID ";
            SqlCommand cmd1 = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
            DataSet ds = new DataSet();
            //autoid();

        }

        private void button3_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=NARMA-PC\SQLEXPRESS;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            if (medid.Text != "" && amount.Text != "")
            {
                try
                {

                    string query = "insert into d_withdraw values('" + wdid.Text + "','" + medid.SelectedValue + "' , '" + amount.Text + "')";
                    SqlCommand cmd = new SqlCommand(query, Conn);
                    cmd.ExecuteNonQuery();


                    string sql1 = "SELECT MEDCNM,AMOUNT FROM d_withdraw,madecine where d_withdraw.MEDCCD=madecine.MEDCCD and  WDID='" + wdid.Text + "' ";
                    SqlCommand cmd1 = new SqlCommand(sql1, Conn);
                    SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
                    DataSet ds = new DataSet();
                    odbcDA.Fill(ds);

                    //MessageBox.Show("เพิ่มข้อมูลแล้ว");
                    this.dgvwd.DataSource = ds.Tables[0];
                    DataGridViewCellStyle cs = new DataGridViewCellStyle();
                    cs.Font = new Font("MS Sans Serif", 12, FontStyle.Regular);

                    this.dgvwd.ColumnHeadersDefaultCellStyle = cs;
                    this.dgvwd.Columns[0].HeaderText = "ชื่อยาที่เบิก";
                    this.dgvwd.Columns[1].HeaderText = "จำนวนที่เบิก";


                    this.dgvwd.Columns[0].Width = 250;
                    this.dgvwd.Columns[1].Width = 100;

                }
                catch (Exception errToAdd)
                {
                    // showgrid();
                    MessageBox.Show("ข้อมูลผิดพลาดเนื่องจาก" + errToAdd.Message, "ผิดพลาด");
                }
            }
            else
            {
                MessageBox.Show("ข้อมูลไม่ครบ", "ผิดพลาด");
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();


            string query = "delete from d_withdraw where WDID = '" + wdid.Text + "'and MEDCCD='" + medid.SelectedValue + "'";
            SqlCommand cmd = new SqlCommand(query, Conn);
            cmd.ExecuteNonQuery();
            string sql1 = "SELECT MEDCCD,AMOUNT FROM d_withdraw where  WDID='" + wdid.Text + "' ";
            //string sql = "SELECT WDID,OSMFNM,DATECD FROM  withdraw,osm where withdraw.OSMID=osm.OSMID and ((withdraw.WDID) = '" + wdid.Text + "')";
            SqlCommand cmd1 = new SqlCommand(sql1, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
            DataSet ds = new DataSet();

            
            odbcDA.Fill(ds);
          
        }

        private void cleardata()
            {
                medid.Text = "";
                amount.Text = "";
            }

        private void button5_Click(object sender, EventArgs e)
        {
            cleardata();
        }

       
        private void button10_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void autoid()
        {
            DataTable max = new DataTable();
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sql = "SELECT WDID FROM withdraw";
            SqlCommand cmd1 = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
            odbcDA.Fill(max);

            DataSet ds = new DataSet();
            odbcDA.Fill(ds, "withdraw");
            SqlDataReader reader = cmd1.ExecuteReader();
            int max_id = 0;
            string max_data = "";

            Conn.Close();
            if (max.Rows.Count != 0)
            {
                for (int i = 0; i < max.Rows.Count; i++)
                {
                    max_data = max.Rows[i]["WDID"].ToString();
                    if (max_id < Convert.ToInt32(max_data))
                    {
                        max_id = Convert.ToInt32(max_data);
                    }
                }
                max_id++;
                max_data = Convert.ToString(max_id);
                if (max_data.Length == 1)
                {
                    max_data = "00000" + max_data;
                }
                else if (max_data.Length == 2)
                {
                    max_data = "0000" + max_data;
                }
                else if (max_data.Length == 3)
                {
                    max_data = "000" + max_data;
                }
                else if (max_data.Length == 4)
                {
                    max_data = "00" + max_data;
                }

                else if (max_data.Length == 5)
                {
                    max_data = "0" + max_data;
                }


                wdid.Text = max_data;
            }
            else
            {
                wdid.Text = "000001";
            }
        }
        

      

        private void medid_SelectedIndexChanged(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sql1 = "select * from madecine where MEDCNM = '" + medid.Text + "'";
            SqlCommand cmd1 = new SqlCommand(sql1, Conn);
            SqlDataReader reader = cmd1.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {

                    textBox1.Text = reader["DIST"].ToString();
                    comboBox1.Text = reader["UNIT"].ToString();
                }

                reader.Close();

            }
        }

        private void button12_Click(object sender, EventArgs e)
        {
            frmRewith frw = new frmRewith();
            frw.ShowDialog();
        }
}
}
